﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;

namespace DAO
{
    public class TraCuuDAO : DataProvider
    {
        public List<TraCuuListDTO> TraCuu(TraCuucrtDTO dto, TraCuuDTO TC)
        {
            SqlConnection conn = MoKetNoi();
            //string sql = TaoCauTruyVan(dto, TC);
            SqlCommand cmd = TaoCauTruyVan(dto,TC);
            cmd.Connection = conn;
            List<TraCuuListDTO> dstc = new List<TraCuuListDTO>();
            SqlDataReader reader = cmd.ExecuteReader();
            while(reader.Read())
            {
                TraCuuListDTO t = new TraCuuListDTO();
                t.MaPhong = reader.GetString(0);
                t.TenPhong = reader.GetString(1);
                t.TenLoaiPhong = reader.GetString(2);
                t.TinhTrang = reader.GetString(3);
                t.Gia = reader.GetInt32(4);
                dstc.Add(t);
            }
            DongKetNoi(conn);
            return dstc;
        }

        private SqlCommand TaoCauTruyVan(TraCuucrtDTO dto, TraCuuDTO TC)
        {
            SqlCommand cmd = new SqlCommand();
            string MaPhong = " 1=1 ";
            string TenPhong = " 1=1 ";
            string TenLoaiPhong = " 1=1 ";
            string TinhTrang = " 1=1 ";
            string Gia = " 1=1 ";
            if (dto.MaPhong == true)
            {
                MaPhong = " Ma_Phong = @MaPhong ";
                cmd.Parameters.Add("@MaPhong", SqlDbType.VarChar);
                cmd.Parameters["@MaPhong"].Value = TC.MaPhong;
            }

            if (dto.TenPhong == true)
            {
                TenPhong = " TenPhong LIKE @TenPhong ";
                cmd.Parameters.Add("@TenPhong", SqlDbType.NVarChar);
                cmd.Parameters["@TenPhong"].Value = "%" + TC.TenPhong + "%";
            }

            if (dto.TenLoaiPhong == true)
            {
                TenLoaiPhong = " TenLoaiPhong LIKE @TenLoaiPhong ";
                cmd.Parameters.Add("@TenLoaiPhong", SqlDbType.NVarChar);
                cmd.Parameters["@TenLoaiPhong"].Value = "%" + TC.TenLoaiPhong + "%";
            }

            if (dto.TinhTrang == true)
            {
                TinhTrang = " TinhTrang = @TinhTrang ";
                cmd.Parameters.Add("@TinhTrang", SqlDbType.VarChar);
                cmd.Parameters["@TinhTrang"].Value = TC.TinhTrang;
            }

            if (dto.GiaCa == true)
            {
                Gia = " Gia BETWEEN @GiaThapNhat AND @GiaCaoNhat ";
                cmd.Parameters.Add("@GiaThapNhat", SqlDbType.Int);
                cmd.Parameters["@GiaThapNhat"].Value = TC.GiaThapNhat;
                cmd.Parameters.Add("@GiaCaoNhat", SqlDbType.Int);
                cmd.Parameters["@GiaThapNhat"].Value = TC.GiaCaoNhat;
            }

            string sql = "SELECT P.Ma_Phong, P.TenPhong, LP.TenLoaiPhong, P.TinhTrang, LP.Gia FROM Phong P, LoaiPhong LP WHERE ";
            sql += MaPhong + " AND ";
            sql += TenPhong + " AND ";
            sql += TenLoaiPhong + " AND ";
            sql += TinhTrang + " AND ";
            sql += Gia + " AND ";
            sql += " P.Ma_LoaiPhong = LP.Ma_LoaiPhong ";

            //string sql;
            //string temp = "";
            //sql = "SELECT P.Ma_Phong, P.TenPhong, LP.TenLoaiPhong, P.TinhTrang, LP.Gia FROM Phong P, LoaiPhong LP WHERE ";

            //if (dto.MaPhong == true)
            //{
            //    temp += " P.Ma_Phong = " + TC.MaPhong;
            //}

            //if (dto.TenPhong == true)
            //{
            //    if (temp != "")
            //    {
            //        temp += " AND P.TenPhong LIKE '" + "%" + TC.TenPhong + "%" + "' ";
            //    }
            //    temp += " P.TenPhong LIKE %" + TC.TenPhong + "%";
            //}

            //if (dto.TenLoaiPhong == true)
            //{
            //    if (temp != "")
            //    {
            //        temp += " AND LP.TenLoaiPhong LIKE '%" + TC.TenLoaiPhong + "%'";
            //    }
            //    temp += " LP.TenLoaiPhong LIKE '%" + TC.TenLoaiPhong + "%'";
            //}

            //if (dto.TinhTrang == true)
            //{
            //    if (temp != "")
            //    {
            //        temp += " AND P.TinhTrang = " + TC.TinhTrang;
            //    }
            //    temp += " P.TinhTrang = " + TC.TinhTrang;
            //}

            //if (dto.GiaCa == true)
            //{
            //    if (temp != "")
            //    {
            //        temp += " AND LP.Gia BETWEEN " + TC.GiaThapNhat + " AND " + TC.GiaCaoNhat;
            //    }
            //    temp += " LP.Gia BETWEEN " + TC.GiaThapNhat + " AND " + TC.GiaCaoNhat;
            //}

            //if (dto.MaPhong == true || dto.TenPhong == true || dto.TenLoaiPhong == true || dto.TinhTrang == true || dto.GiaCa == true)
            //{
            //    temp += " AND P.Ma_LoaiPhong = LP.Ma_LoaiPhong";
            //}

            //if (temp != "")
            //{
            //    sql += temp;
            //}
            //else
            //{
            //    sql = "";
            //}
            cmd.CommandText = sql;
            return cmd;
        }

        public List<string> LoadMaPhong() 
        {
            SqlConnection conn = MoKetNoi();
            string sql = "SELECT Ma_Phong FROM Phong";
            SqlCommand cmd = new SqlCommand(sql, conn);
            List<string> ds = new List<string>();
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                string s;
                s = reader.GetString(0);
                ds.Add(s);
            }
            DongKetNoi(conn);
            return ds;
        }
    }
}
